﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Ruandao.MiniAccess.SqlServer
{
    /// <summary>
    /// 
    /// </summary>
    public class SqlServerTableMapGenerator
    {
        SqlDatabase database;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="database"></param>
        public SqlServerTableMapGenerator(SqlDatabase database)
        {
            this.database = database;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityTypes"></param>
        /// <returns></returns>
        public IDictionary<string, TableMap> GetTableMaps(Type[] entityTypes)
        {
            var tableMaps = new Dictionary<string, TableMap>();
            foreach (var entityType in entityTypes)
            {
                TableMap tableMap = GetEntityMapData(entityType);
                if (tableMap != null)
                {
                    tableMaps.Add(entityType.FullName, tableMap);
                }
            }
            return tableMaps;
        }


        /// <summary>
        /// 根据Attribute来获取映射数据。在具体的实体类中的Attribute由工具生成。
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        public TableMap GetEntityMapData(Type entityType)
        {
            string tableName = entityType.Name;
            if (tableName.EndsWith("Entity"))
            {
                tableName = tableName.Substring(0, tableName.Length - 6);
            }
            TableMapAttribute tma;
            object[] attrs = entityType.GetCustomAttributes(typeof(TableMapAttribute), false);
            if (attrs.Length == 1 && attrs[0] is TableMapAttribute)
            {
                tma = attrs[0] as TableMapAttribute;
                if (!string.IsNullOrEmpty(tma.TableName)) tableName = tma.TableName;
            }
            var tableMap = GetTableMapFromDb(tableName);
            //tableMap.SetEntityAttribute(tma);
            return tableMap;
        }

        #region 私有工具函数
        /// <summary>
        /// 根据DbType类型得到相应的.net framework中的系统类型。
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public System.Type GetSystemType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.Int64: return typeof(Int64);
                case DbType.Binary: return typeof(Byte[]);
                case DbType.Boolean: return typeof(Boolean);
                case DbType.String:
                case DbType.StringFixedLength: return typeof(String);
                case DbType.Date:
                case DbType.DateTime:
                case DbType.DateTime2: return typeof(DateTime);
                case DbType.DateTimeOffset: return typeof(DateTimeOffset);
                case DbType.Decimal: return typeof(Decimal);
                case DbType.Double: return typeof(Double);
                case DbType.Int32: return typeof(Int32);
                case DbType.Single: return typeof(Single);
                case DbType.Int16: return typeof(Int16);
                case DbType.Object: return typeof(Object);
                case DbType.Time: return typeof(TimeSpan);
                case DbType.Byte: return typeof(Byte);
                case DbType.Guid: return typeof(Guid);

                default:
                    throw new NotSupportedException("无法转换为.Net Framework中的数据类型的DbType类型：" + dbType + "。");
            }
        }

        /// <summary>
        /// 根据SqlServer的字段类型名称，得到相应的DbType类型。
        /// </summary>
        public DbType GetDbType( string typeName)
        {
            switch (typeName.ToLower())
            {
                case "bigint":
                    return DbType.Int64;
                case "binary":
                    return DbType.Binary;
                case "bit":
                    return DbType.Boolean;
                case "char":
                    return DbType.String;
                case "date":
                    return DbType.Date;
                case "datetime":
                    return DbType.DateTime;
                case "datetime2":
                    return DbType.DateTime2;
                case "datetimeoffset":
                    return DbType.DateTimeOffset;
                case "decimal":
                    return DbType.Decimal;
                case "float":
                    return DbType.Double;
                case "image":
                    return DbType.Binary;
                case "int":
                    return DbType.Int32;
                case "money":
                    return DbType.Decimal;
                case "nchar":
                    return DbType.StringFixedLength;
                case "ntext":
                    return DbType.String;
                case "numeric":
                    return DbType.Decimal;
                case "nvarchar":
                    return DbType.String;
                case "real":
                    return DbType.Single;
                case "rowversion":
                    return DbType.Binary;
                case "smallint":
                    return DbType.Int16;
                case "smallmoney":
                    return DbType.Decimal;
                case "sql_variant":
                    return DbType.Object;
                case "text":
                    return DbType.String;
                case "time":
                    return DbType.Time;
                case "timestamp":
                    return DbType.Binary;
                case "tinyint":
                    return DbType.Byte;
                case "uniqueidentifier":
                    return DbType.Guid;
                case "varbinary":
                    return DbType.Binary;
                case "varchar":
                    return DbType.String;
                case "xml":
                    return DbType.Xml;

                // case "cursor":
                default:
                    throw new NotSupportedException("无法转换为DbType类型的字段类型名称：" + typeName + "。");
            }
        }
        /// <summary>
        /// 得到字段的类型声明字符串。可用于储存过程中的参数声明。
        /// <br/>异常：NotSupportedException(不支持的字段类型)
        /// </summary>
        /// <param name="column">列的架构。</param>
        /// <returns>字段类型的声明字符串。</returns>
        /// <remarks>查看SqlServer200的联机帮助，一共有24种基本的数据类型。对于不同的类型，有的需要在类型名称之后添加大小。
        /// <br/>
        /// 受支持的类型有：<list type="bullet">
        /// <item><description>整数：tinyint、Smallint、int、bigint</description></item>
        /// <item><description>日期时间：datetime、smalldatetime</description></item>
        /// <item><description>唯一表示：timestamp、uniqueidentifier</description></item>
        /// <item><description>文本：text、ntext、image</description></item>
        /// <item><description>货币和数值：money、smallmoney、float、real、bit</description></item>
        /// <item><description>声明时需要指明大小：char、varchar、nchar、nvarchar、binary、varbinary</description></item>
        /// <item><description>声明时需要指明精度和小数位数：decimal</description></item>
        /// </list>其他类型无法处理，如cursor。</remarks>
        public string GetColumnDeclareType( ColumnMap column)
        {
            switch (column.TypeName.ToLower())
            {
                // 声明时无需指明大小：
                case "tinyint":
                case "smallint":
                case "int":
                case "bigint":

                case "datetime":
                case "smalldatetime":

                case "timestamp":
                case "uniqueidentifier":

                case "text":
                case "ntext":
                case "image":

                case "money":
                case "smallmoney":
                case "float":
                case "real":  // 等同于float(24)
                case "bit":
                    return column.TypeName;

                // 声明时需要指明大小：
                case "char":
                case "varchar":
                case "binary":
                case "varbinary":
                    if (column.Length == -1)
                    {
                        return column.TypeName + "(Max)";
                    }
                    return column.TypeName + "(" + column.Length + ")";

                // 声明大小事按字符计算，而不是按字节计算，所以对于Unicode类型要计算声明长度：
                case "nchar":
                case "nvarchar":
                    if (column.Length == -1)
                    {
                        return column.TypeName + "(Max)";
                    }
                    return column.TypeName + "(" + column.Length/2 + ")";

                // 声明时需要指明精度和小数位数：
                case "numeric":
                case "decimal":
                    return column.TypeName + "(" + column.Prec + "," + column.Scale + ")";

                // 其他类型无法处理：
                // case "cursor":
                default:
                    throw new NotSupportedException("内部错误：无法处理数据类型<" + column.TypeName + ">。");
            }

        }

        /// <summary>
        /// 获取指定数据库中的表的架构。
        /// <br/>异常：ArgumentException(指定的数据库表不存在),
        /// NotSupportedException(表中有不止一个的标识列)
        /// 
        ///  ---  进一步改写时，去掉生成存储过程的功能，改成在部署网站时手工建立相关的数据库对象，包括存储过程、视图等等。
        /// 
        /// </summary>
        /// <param name="tableName">表名。</param>
        public TableMap GetTableMapFromDb(string tableName)
        {
            IDataReader reader = database.ExecuteReader(CommandType.Text, "sp_help [" + tableName + "]");
            if (reader == null)
            {
                throw new ArgumentException("指定的数据库表不存在。", "tableName");
            }
            if (!reader.NextResult()) // to second resultset
            {
                throw new ArgumentException("无法获取指定数据库表的架构。", "tableName");
            }
            Dictionary<string, ColumnMap> columnMaps = new Dictionary<string, ColumnMap>();
            // 列信息在返回的第二个结果集中,列名如下面的代码中所示。
            ParseColumnTable(columnMaps, reader);

            // 标识列信息在返回的第三个结果集中。字段是：Identity, Seed, Increment, Not For Replication。
            // 如果表中没有标识列，则第一个字段的值为：No identity column defined.
            if (!reader.NextResult()) // to third resultset
            {
                throw new ArgumentException("无法获取指定数据库表的架构。", "tableName");
            }

            string identityColumn = "";
            reader.Read();
            if (reader["Identity"].ToString() != "No identity column defined.")
            {
                identityColumn = reader["Identity"].ToString();
            }

            // 第四个表只有字段RowGuidCol，如果没有相应的列，则取值是：No rowguidcol column defined.
            // 第五个表只有字段Data_located_on_filegroup，取值是表数据所存放的文件组，如：PRIMARY（主文件组）。
            if (!reader.NextResult()) // to forth resultset
            {
                throw new ArgumentException("无法获取指定数据库表的架构。", "tableName");
            }
            if (!reader.NextResult()) // to fifth resultset
            {
                throw new ArgumentException("无法获取指定数据库表的架构。", "tableName");
            }

            List<string> primaryKeys = new List<string>();
            List<string> foreignKeys = new List<string>();
            // 如果存在，下一个表是索引，字段是：index_name, index_description, index_keys。如果没有索引（包括主键），则该表不存在。
            // 如果存在，下一个表是约束信息，及主键外键的定义均在该表中。字段是：constraint_type, constraint_name, delete_action, update_action, status_enabled, status_for_replication, constraint_keys。
            if (reader.NextResult()) // if exist sixth resultset
            {
                if (!IsExistColumn(reader, "constraint_type")) // 如果不是约束表，则接着找后面的表。
                {
                    if (reader.NextResult()) // if no exist seventh resultset
                    {
                        if (IsExistColumn(reader, "constraint_type")) // 如果是约束表，则读取约束信息。
                        {
                            ParseConstraintTable(reader, primaryKeys, foreignKeys);
                        }
                    }

                }
            }
            var tableMap = new TableMap(tableName, columnMaps, primaryKeys, foreignKeys);
            tableMap.IdentityColumn = identityColumn;

            SetDefaultColumnMaps(tableMap);
            SetDefaultCommand(tableMap);
            return tableMap;
        }

        private void ParseColumnTable(Dictionary<string, ColumnMap> columnMaps, IDataReader reader)
        {
            while (reader.Read())
            {
                var column = new ColumnMap();
                column.ColumnName = reader["Column_name"].ToString();
                column.TypeName = reader["Type"].ToString();
                column.DbType = GetDbType(column.TypeName);
                column.SystemType = GetSystemType(column.DbType);
                column.Computed = reader["Computed"].ToString();
                column.Length = int.Parse(reader["Length"].ToString());
                column.Prec = reader["Prec"].ToString();
                column.Scale = reader["Scale"].ToString();
                column.Nullable = reader["Nullable"].ToString();

                column.DeclareType = GetColumnDeclareType(column);
                columnMaps.Add(column.ColumnName, column);
            }
        }
        private bool IsExistColumn(IDataReader reader, string columnName)
        {
            try
            {
                reader.GetOrdinal(columnName);
            }
            catch (IndexOutOfRangeException)
            {
                return false;
            }
            return true;
        }
        private void ParseConstraintTable(IDataReader reader, List<string> primaryKeys, List<string> foreignKeys)
        {
            while (reader.Read())
            {
                if (reader["constraint_type"].ToString().StartsWith("PRIMARY KEY"))
                {
                    foreach (string key in (reader["constraint_keys"].ToString().Split(',', ' ')))
                    {
                        if (!string.IsNullOrEmpty(key)) primaryKeys.Add(key);
                    }
                }
                if (reader["constraint_type"].ToString().StartsWith("FOREIGN KEY"))
                {
                    foreach (string key in (reader["constraint_keys"].ToString().Split(',', ' ')))
                    {
                        if (!string.IsNullOrEmpty(key)) foreignKeys.Add(key);
                    }
                }
            }
        }

        private void SetDefaultCommand(TableMap tableMap)
        {
            tableMap.InsertCommand = CreateInsertProcedure(tableMap);
            tableMap.DeleteByConditionsCommand = BuildDeleteCommandText(tableMap);
            tableMap.SearchCommand = BuildSearchCommandText(tableMap);
            if (tableMap.PrimaryKeyColumnMaps.Count > 0)
            {
                tableMap.DeleteCommand = CreateDeleteProcedure(tableMap);
                tableMap.UpdateCommand = CreateUpdateProcedure(tableMap);
                tableMap.FetchCommand = CreateFetchProcedure(tableMap);
            }
        }

        /// <summary>
        /// 根据数据库中的表架构自动生成默认映射数据。
        /// </summary>
        /// <remarks>自动映射的规则是：<list>
        /// <item><description>主键、外键和其他字段分别映射为PrimaryKeyMap、ManyToOneMap和PropertyMap。</description></item>
        /// <item><description>标识字段不允许插入和更新。</description></item>
        /// <item><description>作为主键的Guid类型的字段不允许更新，但是插入时要提供值。</description></item>
        /// <item><description>字段转换为属性名的规则：第一个字符大写，空格用下划线代替。</description></item>
        /// </list>
        /// </remarks>
        private void SetDefaultColumnMaps(TableMap tableMap)
        {
            foreach (ColumnMap cs in tableMap.AllColumnMaps)
            {
                if (tableMap.IsPrimaryKey(cs.ColumnName))
                {
                    cs.ColumnMapType = ColumnMapType.PrimaryKeyMap;
                }
                else if (tableMap.IsForeignKey(cs.ColumnName))
                {
                    cs.ColumnMapType = ColumnMapType.ManyToOneMap;
                }
                else
                {
                    cs.ColumnMapType = ColumnMapType.PropertyMap;
                }
                cs.PropertyName = GetPropertyName(cs.ColumnName);
                cs.IsInsert = true;
                cs.IsUpdate = true;
                if (tableMap.IsIdentity(cs.ColumnName))
                {
                    cs.IsInsert = false;
                    cs.IsUpdate = false;
                }
                if (tableMap.IsPrimaryKey(cs.ColumnName) && ( cs.TypeName.ToLower() == "uniqueidentifier" || cs.SystemType == typeof(System.Int32 )) )
                {
                    cs.IsUpdate = false;
                }
            }
        }

        /*
        private void SetTableMapFromXml(EntityTag entity)
        {
            if (entity.CommandType != null)
                this.CommandType = (CommandType)Enum.Parse(typeof(CommandType), entity.CommandType);

            if (entity.Insert != null)
                this.InsertCommand = entity.Insert;

            if (entity.Delete != null)
                this.DeleteCommand = entity.Delete;

            if (entity.MultipleDelete != null)
                this.DeleteByConditionsCommand = entity.MultipleDelete;

            if (entity.Update != null)
                this.UpdateCommand = entity.Update;

            if (entity.Fetch != null)
                this.FetchCommand = entity.Fetch;

            if (entity.Search != null)
                this.SearchCommand = entity.Search;
        }

        private void SetColumnMapsFromXml(IList<PropertyTag> properties)
        {
            foreach (PropertyTag property in properties)
            {
                string name = property.Name;
                string column = property.Column;
                if (column == null)
                {
                    column = name;
                }
                foreach (ColumnMap cm in ColumnMaps)
                {
                    if (cm.ColumnName == column)
                    {
                        cm.PropertyName = name;
                        cm.ColumnMapType = property.MapType;
                        if (property.Update != null)
                            cm.IsUpdate = (property.Update.ToLower() == "true" ? true : false);

                        if (property.Insert != null)
                            cm.IsInsert = (property.Insert.ToLower() == "true" ? true : false);
                        break;
                    }
                }
            }

        }
        */

        /// <summary>
        /// 把字段名转换为属性名。规则是：第一个字符大写，空格用下                                                                            划线代替。
        /// </summary>
        /// <param name="colname">字段名。</param>
        /// <returns>属性名。</returns>
        private static string GetPropertyName(string colname)
        {
            string s = colname.Substring(0, 1).ToUpper() + colname.Substring(1);
            return String.Join("_", s.Split(' '));
        }

        /// <summary>
        /// 处理数据库中的空字段。
        /// </summary>
        /// <param name="fieldValue">字段值。</param>
        /// <returns>如果字段值为DBNull则返回null, 否则返回字符串值。</returns>
        private static string GetFieldValue(object fieldValue)
        {
            return (fieldValue is DBNull) ? null : (string)fieldValue;
        }

        #endregion

        #region 创建存储过程的公有成员
        /// <summary>
        /// 创建插入记录的存储过程。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>存储过程的名字。</returns>
        public string CreateInsertProcedure(TableMap tableMap)
        {
            string procname = "Insert_" + TableNameForProcedure(tableMap.TableName);

            // 声明参数
            StringBuilder parameters = new StringBuilder();
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.AllColumnMaps)
            {
                if (!isFirst) parameters.Append(",\r\n");
                parameters.Append("\t" + GetParameterDeclaration(cm));
                if ( cm.ColumnName == tableMap.IdentityColumn)
                {
                    parameters.Append(" OUTPUT");
                }
                if (isFirst) isFirst = false;
            }

            string sql = BuildInsertSql(tableMap);

            // 如果有自动增加的标识列，则在输出参数中输出。
            if ( !string.IsNullOrEmpty(tableMap.IdentityColumn) )
            {
                sql += "SET @" + tableMap.IdentityColumn + " = @@Identity\r\n";
            }
            database.CreateProcedure(procname, parameters.ToString(), sql, true);

            return procname;
        }

        /// <summary>
        /// 创建根据主键删除数据库中记录的存储过程。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>存储过程的名字。</returns>
        public string CreateDeleteProcedure(TableMap tableMap)
        {
            string procname = "Delete_" + TableNameForProcedure(tableMap.TableName);

            database.CreateProcedure(procname, GetKeyParameters(tableMap), BuildDeleteSql(tableMap), true);
            return procname;
        }

        /// <summary>
        /// 创建根据主键更新数据库中记录的存储过程。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>存储过程的名字。</returns>
        public string CreateUpdateProcedure(TableMap tableMap)
        {
            string procname = "Update_" + TableNameForProcedure(tableMap.TableName);

            // 声明参数
            string prefix = "";
            StringBuilder parameters = new StringBuilder();
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.AllColumnMaps)
            {
                if (cm.IsUpdate)
                {
                    if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
                    {
                        prefix = "New_";
                    }
                    else
                    {
                        prefix = "";
                    }
                    if (!isFirst) parameters.Append(",\r\n");
                    parameters.Append("\t" + GetParameterDeclaration(cm, prefix));
                    if (isFirst) isFirst = false;
                }
            }

            foreach (ColumnMap cm in tableMap.PrimaryKeyColumnMaps)
            {
                parameters.Append(",\r\n\t" + GetParameterDeclaration(cm));
            }

            database.CreateProcedure(procname, parameters.ToString(), BuildUpdateSql(tableMap), true);
            return procname;
        }

        /// <summary>
        /// 创建根据主键选择数据库中一条记录的存储过程。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>存储过程的名字。</returns>
        public string CreateFetchProcedure( TableMap tableMap)
        {
            string procname = "Select_" + TableNameForProcedure(tableMap.TableName) + "_By_PrimaryKey";

            database.CreateProcedure(procname, GetKeyParameters(tableMap), BuildSelectByKeysSql(tableMap), true);
            return procname;
        }

        #endregion

        #region 创建SQL语句的公有成员

        /// <summary>
        /// 构建插入记录的Insert语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>INSERT 语句的文本。</returns>
        public string BuildInsertSql(TableMap tableMap )
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO [" + tableMap.TableName + "] (\r\n");

            // insert 列表
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.AllColumnMaps)
            {
                if (cm.IsInsert)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    sb.Append("\t" + ColumnNameString(cm.ColumnName));
                    if (isFirst) isFirst = false;
                }
            }
            sb.Append("\r\n)\r\nVALUES\r\n(\r\n");

            // 值的列表
            isFirst = true;
            foreach (ColumnMap cm in tableMap.AllColumnMaps)
            {
                if (cm.IsInsert)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    sb.Append("\t" + ParameterString(cm.ColumnName));
                    if (isFirst) isFirst = false;
                }
            }
            sb.Append("\r\n)\r\n");

            return sb.ToString();
        }

        /// <summary>
        /// 构建根据主键值删除一条记录的DELETE语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>DELETE语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，将抛出异常。
        /// </remarks>
        public string BuildDeleteSql(TableMap tableMap)
        {
            return "DELETE FROM [" + tableMap.TableName+ "] " + WhereByKeysString(tableMap);
        }

        /// <summary>
        /// 构建根据条件删除数据库记录的DELETE语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>DELETE语句的文本。</returns>
        /// <remarks>条件参数的参数名称缺省使用 @Conditions。执行时@Conditions参数的值必须是一个合法的WHERE子句，以及后面可以有连接、OrderBy等字句。</remarks>
        public string BuildDeleteCommandText( TableMap tableMap)
        {
            return "DELETE FROM [" + tableMap.TableName + "] ";  // 注意：该字符串以空格结尾，用来和后面的条件语句隔开。
        }

        /// <summary>
        /// 构建根据主键更新记录的UPDATE语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>UPDATE语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，或者表中没有可更新的字段，都将抛出异常。
        /// </remarks>
        public string BuildUpdateSql(TableMap tableMap)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE" + " [" + tableMap.TableName + "]\r\nSET\r\n");

            string prefix = "";
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.AllColumnMaps)
            {
                if (cm.IsUpdate)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
                    {
                        prefix = "New_";
                    }
                    else
                    {
                        prefix = "";
                    }
                    sb.Append("\t" + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName, prefix));
                    if (isFirst) isFirst = false;
                }
            }

            if (isFirst == true)
            {
                throw new OrMapException("表 <" + tableMap.TableName + "> 没有可以更新的字段。");
            }

            sb.Append("\r\n");
            sb.Append(WhereByKeysString(tableMap));
            return sb.ToString();
        }

        /// <summary>
        /// 根据主键查找记录的SQL 语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>SELECT 语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，将抛出异常。
        /// </remarks>
        public string BuildSelectByKeysSql(TableMap tableMap)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(GetSelectString(tableMap.TableName, tableMap.AllColumnMaps));

            sb.Append("FROM [" + tableMap.TableName + "]\r\n");
            sb.Append(WhereByKeysString(tableMap));
            return sb.ToString();
        }

        /// <summary>
        /// 构造按条件查询的Select语句。
        /// </summary>
        /// <param name="tableMap">实体对应的数据库中的表的映射。</param>
        /// <returns>SELECT 语句的文本。</returns>
        public string BuildSearchCommandText(TableMap tableMap)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(GetSelectString(tableMap.TableName, tableMap.AllColumnMaps));

            sb.Append("\tFROM [" + tableMap.TableName + "] "); //注意：返回的字符串以空格结尾，作用是和后面的条件字符串隔开
            return sb.ToString();
        }

        #endregion

        #region 私有工具函数

        // 字符串以回车换行结尾。
        private string GetKeyParameters(TableMap tableMap)
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.PrimaryKeyColumnMaps)
            {
                if (!isFirst) sb.Append(",\r\n");
                sb.Append("\t" + GetParameterDeclaration(cm));
                if (isFirst) isFirst = false;
            }
            sb.Append("\r\n");
            return sb.ToString();
        }

        private static string GetParameterDeclaration(ColumnMap columnMap)
        {
            return GetParameterDeclaration(columnMap, "");
        }

        // 得到列数据的参数形式的字符串。
        private static string GetParameterDeclaration(ColumnMap columnMap, string prefix)
        {
            return "@" + prefix + String.Join("_", columnMap.ColumnName.Split(' ')) + " " + columnMap.DeclareType;
        }

        private static string TableNameForProcedure(string s)
        {
            return String.Join("_", s.Split(' '));
        }

        // 字符串开始无空格，字符串以回车换行结尾。
        private static string GetSelectString(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT\r\n");
            bool isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (!isFirst) sb.Append(",\r\n");
                sb.Append("\t[" + tableName + "]." + ColumnNameString(cm.ColumnName));
                if (isFirst) isFirst = false;
            }
            sb.Append("\r\n");
            return sb.ToString();
        }

        // 字符串开始无空格，字符串以回车换行结尾。如果没有主键，将抛出异常。
        private string WhereByKeysString( TableMap tableMap)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("WHERE\r\n");
            bool isFirst = true;
            foreach (ColumnMap cm in tableMap.PrimaryKeyColumnMaps)
            {
                if (!isFirst) sb.Append(" AND\r\n");
                sb.Append("\t" + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName));
                if (isFirst) isFirst = false;
            }
            if (isFirst)
            {
                throw new OrMapException("表" + tableMap.TableName + "没有主键。当使用GetEntity, DeleteEntity和UpdateEntity等函数时，对应的数据库表必须有主键。");
            }
            sb.Append("\r\n");
            return sb.ToString();
        }

        private static string ColumnNameString(string column, string prefix)
        {
            return "[" + prefix + column + "]";
        }

        private static string ColumnNameString(string column)
        {
            return ColumnNameString(column, "");
        }

        // 把空格替换为下划线。因为参数名称不支持空格分隔的名称。
        private static string ParameterString(string column, string prefix)
        {
            return "@" + prefix + String.Join("_", column.Split(' '));
        }

        private static string ParameterString(string column)
        {
            return ParameterString(column, "");
        }

        #endregion

    }
}
